package com.googlecode.maratische.google;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import org.apache.log4j.Logger;

/**
 * инициализация базы даннях для продукта
 * @author Marat Gizatullin <maratische@gmail.com>
 *
 */
public class DBConnect {
	static Logger logger = Logger.getLogger(DBConnect.class.getName());

	Connection conn = null;

	public DBConnect() {
	}

	/**
	 * иницилизируем коннект к базе данных, 
	 * база данных создается в текущем каталоге, если не существует. <br/>
	 * в таблице  VERSIONS храниться версия текущей базы данных, 
	 * если в массиве SQL больше строк, чем версия базы данных, 
	 * то проливаем необходимые строки, после этого изменяем текущую версию базы данных
	 */
	public void init(String userHome) throws ClassNotFoundException,SQLException {
//		String driver = "net.sf.log4jdbc.DriverSpy";//"org.apache.derby.jdbc.EmbeddedDriver";
//		String connectionURL = "jdbc:log4jdbc:derby:"+userHome+"rssreader;create=true";
		String driver = "org.apache.derby.jdbc.EmbeddedDriver";
		String connectionURL = "jdbc:derby:"+userHome+"rssreader;create=true";

		Statement s;

		try {
			Class.forName(driver);
			logger.debug(driver + " loaded. ");
		} catch (java.lang.ClassNotFoundException e) {
			throw e;
		}		
		
		try {
			conn = DriverManager.getConnection(connectionURL);

			int versionId=-1;
			//первое и обязательно, необходимо проверить наличие таблицы VERSIONS
			if (checkTable(conn, "VERSIONS","ID = 0")) {
				String sql = "select ID from VERSIONS";
				s = conn.createStatement();
				ResultSet rs = s.executeQuery(sql);
				if (rs.next()) {
					versionId = rs.getInt("ID");
				}
			}
			s = conn.createStatement();
//			logger.debug("old database version id " +versionId);
			if (versionId < SQL.length-1) {
				while (versionId < SQL.length-1) {
					versionId++;
					logger.debug(SQL[versionId]);
					s.executeUpdate(SQL[versionId]);
				}
				String sql = "update VERSIONS set ID="+versionId;
				logger.debug(sql);
				s.executeUpdate(sql);
			}
//			logger.debug("new database version id " +versionId);
		} catch (SQLException e) {
			throw e;
		}
	}
	
	public Connection getConnection() {
		return this.conn;
	}
	
	/**
	 * проверяем наличие таблицы
	 * когда возвращается код ошибки 42X05, это означает, что данной таблицы в базе данных нет
	 * @param conn
	 * @param tableName
	 * @param testParam
	 * @return
	 * @throws SQLException
	 */
	private boolean checkTable(Connection conn, String tableName, String testParam) throws SQLException {
	      try {
	          Statement s = conn.createStatement();
	          s.execute("update " +tableName+" set "+testParam+" where 1=3");
	       }  catch (SQLException sqle) {
	          String theError = (sqle).getSQLState();
	          logger.info("  Utils GOT:  " + theError);
	          /** If table exists will get -  WARNING 02000: No row was found **/
	          if (theError.equals("42X05"))   // Table does not exist
	          {  return false;
	           }  else if (theError.equals("42X14") || theError.equals("42821"))  {
	 	          logger.info("WwdChk4Table: Incorrect table definition. Drop table WISH_LIST and rerun this program");
	              throw sqle;   
	           } else { 
	 	          logger.info("WwdChk4Table: Unhandled SQLException" );
	              throw sqle; 
	           }
	       }
		return true;
	}
	
	String[] SQL = new String[] {
		"create table VERSIONS(ID INTEGER not null)",
		"insert into VERSIONS (ID) values (1)",
		"create table FEED (ID INTEGER not null GENERATED ALWAYS AS IDENTITY, NAME VARCHAR (255) not null, XMLURL VARCHAR (2000), PARENT_ID INTEGER, OFFLINE_SUPPORT SMALLINT, UNREAD_ITEMS INTEGER,  FEED_DESCRIPTOR_ID VARCHAR(2000), constraint PK_FEED primary key (ID))",
		"create table COMMAND (ID INTEGER not null GENERATED ALWAYS AS IDENTITY, TYPE VARCHAR (50), PRIORITY INTEGER not null, constraint PK_COMMAND primary key (ID))",
		"alter table COMMAND add column STRING1 VARCHAR (500)",
		"alter table COMMAND add column STRING2 VARCHAR (500)",
		"create table ITEM (ID INTEGER not null GENERATED ALWAYS AS IDENTITY, FEED_ID INTEGER not null, " +
		"URI VARCHAR (2000), TITLE VARCHAR(2000), LINK VARCHAR(2000), DESCRIPTION CLOB, constraint PK_ITEM primary key (ID))",
		"alter table ITEM add column ITEM_READ SMALLINT",
		"alter table FEED drop column UNREAD_ITEMS ",
		"alter table ITEM add column READED_DATE TIMESTAMP",
		"update ITEM set READED_DATE=TIMESTAMP('20100726010101') where ITEM_READ = 1 and READED_DATE is NULL",
		"alter table COMMAND add column INTEGER1 INT",
		"create table PROPERTY (ID INTEGER not null GENERATED ALWAYS AS IDENTITY, TYPE VARCHAR (50), STRING1 VARCHAR (500), INTEGER1 INT, constraint PK_PROPERTY primary key (ID))",
		"create table LABEL (ID INTEGER not null GENERATED ALWAYS AS IDENTITY, TYPE VARCHAR (50), COMMENT VARCHAR (500), constraint PK_LABEL primary key (ID))",
		"alter table ITEM add column LABEL_ID INT",
		"alter table ITEM add constraint FK_ITEM_LABEL_1 foreign key (LABEL_ID) references LABEL on delete no action on update no action",
		"alter table ITEM add column UPDATED_DATE TIMESTAMP",
		"create index FEED_PARENT_ID_INDEX on FEED (PARENT_ID)",
		"alter table LABEL add column FILE_SAVED VARCHAR (2000)",
		"create table SPAM (ID INTEGER not null GENERATED ALWAYS AS IDENTITY, TITLE VARCHAR (50), TITLE_TYPE INT, DESCRIPTION VARCHAR (500), DESCRIPTION_TYPE INT, COUNT INT, constraint PK_SPAM primary key (ID))",
		"alter table SPAM add column DATE_STOP TIMESTAMP",
		"insert into FEED (NAME,XMLURL,PARENT_ID,OFFLINE_SUPPORT,FEED_DESCRIPTOR_ID) values ('people_you_follow','',0,1,'people_you_follow')",
		"alter table ITEM add column source_uri VARCHAR(255)",
		"alter table ITEM add column author VARCHAR(255)",
		"create index ITEM_FEED_ID_INDEX on ITEM (FEED_ID)",
		
	};
}
